﻿using Dapper;
using Dapper.Extension;
using Microsoft.Extensions.Options;
using Publicuse.Interface;
using Publicuse.Entity;
using Publicuse.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Z.Dapper.Plus;

namespace Publicuse.Service
{
    public class BaseDapperService : IBaseDapperService
    {
        /// <summary>
        /// 根据连接类型获取数据库连接工厂名称
        /// </summary>
        /// <param name="connectionType"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private string GetSessionName(ConnectionTypeEnum connectionType)
        {
            var sessionname = string.Empty;
            switch (connectionType)
            {
                case ConnectionTypeEnum.SqlServer:
                    sessionname = "SqlServer";
                    break;
                case ConnectionTypeEnum.MySql:
                    sessionname = "MySql";
                    break;
                default:
                    throw new Exception("没有找到连接");
            }
            return sessionname;
        }

        /// <summary>
        /// 根据连接类型获取SQL
        /// </summary>
        /// <param name="connectionType"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private string GetSql<T>(ConnectionTypeEnum connectionType) where T : class
        {
            var sql = string.Empty;
            switch (connectionType)
            {
                case ConnectionTypeEnum.SqlServer:
                    sql = CustomSqlCache<T>.GetQuerySql();
                    break;
                case ConnectionTypeEnum.MySql:
                    sql = CustomMySqlCache<T>.GetQuerySql();
                    break;
                default:
                    throw new Exception("没有找到连接");
            }
            return sql;
        }

        /// <summary>
        /// 初始化连接
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectionType"></param>
        /// <param name="iscommit"></param>
        /// <param name="msg"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        private T InitConnection<T>(ConnectionTypeEnum connectionType, bool iscommit, out string msg, Func<ISession, T> action) where T : class
        {
            msg = string.Empty;
            ISession session = null;
            T entity = default(T);
            try
            {
                session = SessionFactory.GetSession(GetSessionName(connectionType));
                if (iscommit)
                {
                    session?.Open(true);
                }
                entity = action.Invoke(session);
                if (iscommit)
                {
                    session?.Commit();
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                if (ex.InnerException != null)
                {
                    msg += "," + ex.InnerException.Message;
                }
                session?.Rollback();
            }

            return entity;
        }

        private void InitConnection(ConnectionTypeEnum connectionType, bool iscommit, out string msg, Action<ISession> action)
        {
            msg = string.Empty;
            ISession session = null;
            try
            {
                session = SessionFactory.GetSession(GetSessionName(connectionType));
                if (iscommit)
                {
                    if (session.Connection.State != ConnectionState.Open)
                    {
                        session?.Open(true);
                    }
                }
                action.Invoke(session);
                if (iscommit)
                {
                    session?.Commit();
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                if (ex.InnerException != null)
                {
                    msg += "," + ex.InnerException.Message;
                }
                session?.Rollback();
            }

        }

        /// <summary>
        /// 根据连接类型获取SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectionType"></param>
        /// <param name="Id"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private string GetFindByIdSql<T>(ConnectionTypeEnum connectionType, int Id) where T : class
        {
            var sql = string.Empty;
            switch (connectionType)
            {
                case ConnectionTypeEnum.SqlServer:
                    sql = CustomSqlCache<T>.GetFindByIdSql(Id);
                    break;
                case ConnectionTypeEnum.MySql:
                    sql = CustomMySqlCache<T>.GetFindByIdSql(Id);
                    break;
                default:
                    throw new Exception("没有找到连接");
            }
            return sql;
        }

        #region 查询 
        /// <summary>
        /// 查询所有的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> GetAll<T>(ConnectionTypeEnum connectionType, out string msg) where T : class
        {
            return (List<T>)this.InitConnection<List<T>>(connectionType, false, out msg, (session) =>
           {
               string sql = this.GetSql<T>(connectionType);
               return session.Query<T>(sql).ToList();
           });
        }

        /// <summary>
        /// 根据SQL查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> Query<T>(string sql, ConnectionTypeEnum connectionType, out string msg) where T : class
        {
            return this.InitConnection<List<T>>(connectionType, false, out msg, (session) =>
            {
                return session.Query<T>(sql, true, true, 10, CommandType.Text).ToList();
            });
        }

        /// <summary>
        /// 根据条件查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(Expression<Func<T, bool>> expression, ConnectionTypeEnum connectionType, out string msg) where T : class
        {
            return this.InitConnection<IEnumerable<T>>(connectionType, false, out msg, (session) =>
            {
                Dapper.Extension.IQueryable<T> dataQuery = session.From<T>();
                if (expression != null)
                {
                    dataQuery = dataQuery.Where(expression);
                }
                return dataQuery.Select();
            });
        }

        /// <summary>
        /// 通过编号查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public T Find<T>(int id, ConnectionTypeEnum connectionType, out string msg) where T : BaseModel<long>
        {
            return this.InitConnection<T>(connectionType, false, out msg, (session) =>
            {
                string sql = this.GetFindByIdSql<T>(connectionType, id);
                return session.Connection.QueryFirstOrDefault<T>(sql);
            });
        }

        /// <summary>
        /// 在一个查询中处理多个结果
        /// var sql=select * from Commodity where Id>@Id;select * from Company where Id>@Idb;
        /// this.baseDapperService.QueryMultiple(sql,ConnectionTypeEnum.SqlServer, new{ Id=15,Idb=6},out var msg, (list => {
        /// var lista = list.Read<Commodity>().ToList();
        /// var lsitb = list.Read<Company>().ToList();}));
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionType"></param>
        /// <param name="parameters"></param>
        /// <param name="msg"></param>
        /// <param name="action"></param>
        public void QueryMultiple(string sql, ConnectionTypeEnum connectionType, object parameters, out string msg, Action<SqlMapper.GridReader> action)
        {
            this.InitConnection(connectionType, false, out msg, (session) =>
            {
                var multi = session.QueryMultiple(sql, parameters);
                action?.Invoke(multi);
            });
        }

        /// <summary>
        /// 根据sql获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, ConnectionTypeEnum connectionType, out string msg, object parameters)
        {
            return this.InitConnection<DataTable>(connectionType, false, out msg, (session) =>
             {
                 DataTable dataTable = new DataTable();
                 var data = session.Connection.ExecuteReader(sql, parameters);
                 dataTable.Load(data);
                 return dataTable;
             });
        }

        /// <summary>
        /// 根据sql获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, ConnectionTypeEnum connectionType, out string msg, object parameters)
        {
            return this.InitConnection<DataSet>(connectionType, false, out msg, (session) =>
            {
                DataSet dataSet = new DataSet();
                var data = session.Connection.ExecuteReader(sql, parameters);
                dataSet = DataTableUtil.ConvertDataReaderToDataSet(data);
                return dataSet;
            });
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public bool ExecuteSql(string sql, ConnectionTypeEnum connectionType, out string msg, object parameters)
        {
            bool result = false;
            this.InitConnection(connectionType, true, out msg, (session) =>
            {
                result = session.Execute(sql, parameters) > -1;
            });
            return result;
        }

        /// <summary>
        ///  分页查询
        ///  Nuget：Install-Package Dapper.Common -Version 1.5.0   扩展 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="S"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="where"></param>
        /// <param name="orderby"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public PageResult<T> GetPageList<T, S>(int pageIndex, int pageSize, Expression<Func<T, bool>> where, Expression<Func<T, S>> orderby, ConnectionTypeEnum connectionType, out string msg) where T : class
        {
            return this.InitConnection<PageResult<T>>(connectionType, false, out msg, (session) =>
            {
                PageResult<T> pageResult = new PageResult<T>(pageIndex, pageSize);
                Dapper.Extension.IQueryable<T> dataQuery = session.From<T>();
                if (where != null)
                {
                    dataQuery = dataQuery.Where(where);
                }
                if (orderby != null)
                {
                    dataQuery = dataQuery.OrderBy(orderby);
                }
                pageResult.DataList = dataQuery.Page(pageIndex, pageSize, out long totalCount, true).Select().ToList();
                pageResult.TotalCount = totalCount;
                return pageResult;
            });
        }
        #endregion

        #region 增加 
        /// <summary>
        /// 增加实体 
        /// nuget引入:Z.Dapper.Plus
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="connectionType"></param>
        /// <returns></returns>
        public T Insert<T>(T t, ConnectionTypeEnum connectionType, out string msg) where T : BaseModel<long>
        {
            return this.InitConnection<T>(connectionType, true, out msg, (session) =>
            {
                DapperPlusManager.Entity<T>().Table(typeof(T).Name).Identity(c => c.Id);
                DapperPlusActionSet<T> result = session.Connection.BulkInsert(t);
                return result.CurrentItem;
            });
        }

        /// <summary>
        /// 批量添加
        /// Z.Dapper.Plus
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> InsertBatch<T>(List<T> list, ConnectionTypeEnum connectionType, out string msg) where T : BaseModel<long>
        {
            return this.InitConnection<List<T>>(connectionType, true, out msg, (session) =>
            {
                DapperPlusManager.Entity<T>().Table(typeof(T).Name).Identity(c => c.Id);
                DapperPlusActionSet<List<T>> retsult = session.Connection.BulkInsert(list);
                return retsult.CurrentItem;
            });
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改实体
        /// 增加 nuget引入:Z.Dapper.Plus
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public T Update<T>(T t, ConnectionTypeEnum connectionType, out string msg) where T : BaseModel<long>
        {
            return this.InitConnection<T>(connectionType, true, out msg, (session) =>
            {
                DapperPlusActionSet<T> uResult = session.Connection.BulkUpdate(t);
                return uResult.CurrentItem;
            });
        }

        /// <summary>
        /// 批量修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public IEnumerable<T> UpdateInsertBatch<T>(IEnumerable<T> list, ConnectionTypeEnum connectionType, out string msg) where T : BaseModel<long>
        {
            return this.InitConnection<IEnumerable<T>>(connectionType, true, out msg, (session) =>
            {
                DapperPlusActionSet<T> result = session.Connection.BulkUpdate(list);
                return result.Current;
            });
        }

        #endregion

        #region 删除

        /// <summary>
        /// 根据ID删除
        /// 增加 nuget引入:Z.Dapper.Plus
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Delete<T>(int id, ConnectionTypeEnum connectionType, out string msg) where T : BaseModel<long>, new()
        {
            bool result = false;
            this.InitConnection(connectionType, true, out msg, (session) =>
            {
                result = session.From<T>().Where(c => c.Id == id).Delete() > 0;
            });
            return result;
        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="connectionType"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Delete<T>(Expression<Func<T, bool>> where, ConnectionTypeEnum connectionType, out string msg) where T : BaseModel<long>
        {
            bool result = false;
            this.InitConnection(connectionType, true, out msg, (session) =>
            {
                result = session.From<T>().Where(where).Delete() > 0;
            });
            return result;
        }
        #endregion
    }
}
